﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using BGCOP.CAVTOP.BUSINESSLAYER;

namespace importarExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            //subeVehiculos();
            //subeModelos();
            subeTarjetas();
        }

        static void subeTarjetas()
        {
            int i = 0;
            int j = 0;
            Console.WriteLine("*****************************************\n");
            Console.WriteLine("*Importar tarjetas Maestras CavTop  *\n");
            Console.WriteLine("*****************************************\n\n");
            OleDbConnection oConn = new OleDbConnection();
            OleDbCommand oCmd = new OleDbCommand();
            OleDbDataAdapter oDa = new OleDbDataAdapter();
            DataSet oDs = new DataSet();

            String strDestino = @"edifproteccion.xls";
            //String strDestino = @"asignap1.xls";
            oConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                strDestino + ";Extended Properties=Excel 8.0;";

            oCmd.CommandText = @"select * from DATOS";
            oCmd.Connection = oConn;
            oDa.SelectCommand = oCmd;
            oDa.Fill(oDs);
            masterDBACEntities db = new masterDBACEntities();
            foreach (DataRow dtFila in oDs.Tables[0].Rows)
            {
                var cad = dtFila[0].ToString();
                var mm = new TTarjeta()
                             {
                                 numtarjeta=cad,
                                 activa=true 
                             };
                db.AddToTTarjeta(mm);

            }
            Console.WriteLine("Termino 1!");
            db.SaveChanges();
            Console.WriteLine("Termino 2!");
            Console.Read();
        }
        static void subeParqueos2()
        {
            int i = 0;
            int j = 0;
            Console.WriteLine("*****************************************\n");
            Console.WriteLine("*Importar p2 del Sistema CavTop  *\n");
            Console.WriteLine("*****************************************\n\n");
            try
            {
                OleDbConnection oConn = new OleDbConnection();
                OleDbCommand oCmd = new OleDbCommand();
                OleDbDataAdapter oDa = new OleDbDataAdapter();
                DataSet oDs = new DataSet();

                String strDestino = @"edifproteccion.xls";
                //String strDestino = @"asignap1.xls";
                oConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                    strDestino + ";Extended Properties=Excel 8.0;";

                oCmd.CommandText = @"select * from A1979";
                oCmd.Connection = oConn;
                oDa.SelectCommand = oCmd;
                oDa.Fill(oDs);

                TTarjeta mm = new TTarjeta();
                VehiculosParqueos kk = new VehiculosParqueos();

                foreach (DataRow dtFila in oDs.Tables[0].Rows)
                {
                    Console.WriteLine(i++.ToString() + " " + dtFila[0].ToString() + " " +
                                      dtFila[0].ToString() + " " + "\n");
                    TVehiculo tv= new TVehiculo();// = mm.consultarVehiculo(dtFila[0].ToString());
                    switch (Convert.ToInt32(dtFila[1]))
                    {
                        case 0:
                            kk.Modificar(Convert.ToInt32(dtFila[2]), tv.idVehiculo, DateTime.Now,1, Convert.ToInt32(dtFila[1]), d0: true);
                            break;
                        case 1:
                            kk.Modificar(Convert.ToInt32(dtFila[2]), tv.idVehiculo, DateTime.Now,1, Convert.ToInt32(dtFila[1]), d1: true);
                            break;
                        case 2:
                            kk.Modificar(Convert.ToInt32(dtFila[2]), tv.idVehiculo, DateTime.Now,1, Convert.ToInt32(dtFila[1]), d2: true);
                            break;
                        case 3:
                            kk.Modificar(Convert.ToInt32(dtFila[2]), tv.idVehiculo, DateTime.Now,1, Convert.ToInt32(dtFila[1]), d3: true);
                            break;
                        case 4:
                            kk.Modificar(Convert.ToInt32(dtFila[2]), tv.idVehiculo, DateTime.Now,1, Convert.ToInt32(dtFila[1]), d4: true);
                            break;
                        case 5:
                            kk.Modificar(Convert.ToInt32(dtFila[2]), tv.idVehiculo, DateTime.Now,1, Convert.ToInt32(dtFila[1]), d5: true);
                            break;
                        case 6:
                            kk.Modificar(Convert.ToInt32(dtFila[2]), tv.idVehiculo, DateTime.Now,1, Convert.ToInt32(dtFila[1]), d6: true);
                            break;
                        
                    }
                    
                }
            }
            catch (Exception)
            {

                j++;
            }
            Console.WriteLine("Errores encontrados :" + j.ToString() + " / " + i.ToString());
            Console.ReadLine();

        }
        static void subeParqueos()
        {
            int i = 0;
            int j = 0;
            Console.WriteLine("*****************************************\n");
            Console.WriteLine("*Importar Vehiculos del Sistema CavTop  *\n");
            Console.WriteLine("*****************************************\n\n");
            try
            {
                OleDbConnection oConn = new OleDbConnection();
                OleDbCommand oCmd = new OleDbCommand();
                OleDbDataAdapter oDa = new OleDbDataAdapter();
                DataSet oDs = new DataSet();

                //String strDestino = @"ParqueoxVehiculo.xls";
                String strDestino = @"asignap1.xls";
                oConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                    strDestino + ";Extended Properties=Excel 8.0;";

                oCmd.CommandText = @"select * from DTS";
                oCmd.Connection = oConn;
                oDa.SelectCommand = oCmd;
                oDa.Fill(oDs);

                Vehiculos mm = new Vehiculos();
                VehiculosParqueos kk = new VehiculosParqueos();

                foreach (DataRow dtFila in oDs.Tables[0].Rows)
                {
                    Console.WriteLine(i++.ToString() + " " + dtFila[0].ToString() + " " +
                                      dtFila[1].ToString() + " " + "\n");
                    TVehiculo tv = mm.consultarVehiculo(dtFila[0].ToString());
                    kk.Agregar(Convert.ToInt32(dtFila[1]), tv.idVehiculo, DateTime.Now, 1);
                }
            }
            catch (Exception)
            {

                j++;
            }
            Console.WriteLine("Errores encontrados :" + j.ToString() + " / " + i.ToString());
            Console.ReadLine();

        }
        static void subeVehiculos()
        {
            int i = 0;
            int j = 0;
            Console.WriteLine("*****************************************\n");
            Console.WriteLine("*Importar Vehiculos del Sistema CavTop  *\n");
            Console.WriteLine("*****************************************\n\n");
            try
            {
                OleDbConnection oConn = new OleDbConnection();
                OleDbCommand oCmd = new OleDbCommand();
                OleDbDataAdapter oDa = new OleDbDataAdapter();
                DataSet oDs = new DataSet();

                String strDestino = @"Vehiculos.xls";

                oConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                    strDestino + ";Extended Properties=Excel 8.0;";

                oCmd.CommandText = @"select * from DST";
                oCmd.Connection = oConn;
                oDa.SelectCommand = oCmd;
                oDa.Fill(oDs);
                Vehiculos emp = new Vehiculos();
                foreach (DataRow dtFila in oDs.Tables[0].Rows)
                {
                    Console.WriteLine(i++.ToString() + " " + dtFila[0].ToString() + " " +
                                      dtFila[0].ToString() + " " +
                                      "\n");
                    //buscar la modelo del vehiculo
                    var modT = new ModelosVehiculo();
                    int mm = Convert.ToInt32(modT.buscar(dtFila[1].ToString()).idModeloVehiculo);
                    emp.Agregar(dtFila[0].ToString(), mm,
                    codigoBarras: dtFila[6].ToString(), color: dtFila[4].ToString(), fechaC: DateTime.Now, usuarioAct: 1);
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                j++;
            }
            Console.WriteLine("Errores encontrados :" + j.ToString() + " / " + i.ToString());
            Console.ReadLine();
        }
        static void subeModelos()
        {
            int i = 0;
            int j = 0;
            Console.WriteLine("**************************************\n");
            Console.WriteLine("*Importar modelos del Sistema CavTop  *\n");
            Console.WriteLine("**************************************\n\n");
            try
            {
                OleDbConnection oConn = new OleDbConnection();
                OleDbCommand oCmd = new OleDbCommand();
                OleDbDataAdapter oDa = new OleDbDataAdapter();
                DataSet oDs = new DataSet();

                String strDestino = @"marcasVehiculos.xls";
                
                oConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 
                    strDestino + ";Extended Properties=Excel 8.0;";

                oCmd.CommandText = @"select * from DST";
                oCmd.Connection = oConn;
                oDa.SelectCommand = oCmd;
                oDa.Fill(oDs);
                ModelosVehiculo emp = new ModelosVehiculo();
                foreach (DataRow dtFila in oDs.Tables[0].Rows)
                {
                    Console.WriteLine(i++.ToString() + " " + dtFila[0].ToString() + " " +
                                      dtFila[0].ToString() + " " +
                                      "\n");
                    try
                    {
                        emp.CrearModeloVehiculo(dtFila[0].ToString(), DateTime.Now, 1);
                    }
                    catch (Exception)
                    {

                        j++;
                        Console.WriteLine(" Error \n");
                    }
                }
            }
            catch (Exception)
            {
                
                throw;
            }
            Console.WriteLine("Errores encontrados :" + j.ToString() + " / " + i.ToString());
            Console.ReadLine();
        }
        static void subeEmpleados()
        {
            int i = 0;
                int j = 0;
            Console.WriteLine("**************************************\n");
            Console.WriteLine("*Importar Divisiones del Sistema CavTop  *\n");
            Console.WriteLine("**************************************\n\n");
            try
            {
                OleDbConnection oConn = new OleDbConnection();
                OleDbCommand oCmd = new OleDbCommand();
                OleDbDataAdapter oDa = new OleDbDataAdapter();
                DataSet oDs = new DataSet();

                String strDestino = @"empleados.xls";
                oConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDestino + ";Extended Properties=Excel 8.0;";

                oCmd.CommandText = @"select * from Datos";
                oCmd.Connection = oConn;
                oDa.SelectCommand = oCmd;
                oDa.Fill(oDs);
                
                Empleados emp = new Empleados();
                foreach (DataRow dtFila in oDs.Tables[0].Rows)
                {
                    Console.WriteLine(i++.ToString() + " " + dtFila[1].ToString() + " " +
                                      dtFila[2].ToString() + " " +
                                         "\n");
                    try
                    {
                        emp.CrearEmpleado(dtFila[1].ToString(), dtFila[2].ToString(), dtFila[3].ToString(),
                                      dtFila[4].ToString(), dtFila[5].ToString(), dtFila[6].ToString(),
                                      dtFila[8].ToString(),
                                      dtFila[9].ToString(), dtFila[10].ToString(),
                                      1);
                    }
                    catch (Exception)
                    {
                        j++;
                        Console.WriteLine(" Error \n");
                    }
                    
                }
            }
            catch (Exception)
            {
                
                throw;
            }
            Console.WriteLine("Errores encontrados :" + j.ToString() + " / " + i.ToString( ) );
            Console.ReadLine();
        }
        static void subeDivisiones()
        {
            Console.WriteLine("**************************************\n");
            Console.WriteLine("*Importar Divisiones del Sistema CavTop  *\n");
            Console.WriteLine("**************************************\n\n");
            try
            {
                OleDbConnection oConn = new OleDbConnection();
                OleDbCommand oCmd = new OleDbCommand();
                OleDbDataAdapter oDa = new OleDbDataAdapter();
                DataSet oDs = new DataSet();

                String strDestino = @"divisiones.xls";
                oConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDestino + ";Extended Properties=Excel 8.0;";

                oCmd.CommandText = @"select * from Datos";
                oCmd.Connection = oConn;
                oDa.SelectCommand = oCmd;
                oDa.Fill(oDs);
                int i = 0;
                DivisionesEmpresa tmp = new DivisionesEmpresa();
                foreach (DataRow dtFila in oDs.Tables[0].Rows)
                {
                    Console.WriteLine(i++.ToString( ) + " " + dtFila[1].ToString() + " " +
                                      dtFila[2].ToString() + " " +
                                         "\n");
                    var em = new TDivisionEmpresa();
                    em.NombreDivision = dtFila[1].ToString();
                    em.IdEmpresa = Convert.ToInt32(dtFila[2].ToString());
                    em.piso = "Piso " + dtFila[3].ToString();
                    em.fechaUltimaGestion = DateTime.Now;
                    em.usuarioUltimaGestion = 1;
                    try
                    {
                        tmp.AgregarDivision(em);
                    }
                    catch (Exception ex1)
                    {

                        Console.WriteLine(ex1.Message + "  \n" + ex1.InnerException.ToString());
                    }
                    
                    
                }
            }
            catch (Exception ex)
            {

                Console.WriteLine(ex.Message + "  \n" + ex.InnerException.ToString());
            }
            Console.ReadLine();
        }
            static void subeCargos()
        {
            Console.WriteLine("**************************************\n");
            Console.WriteLine("*Importar cargos del Sistema CavTop  *\n");
            Console.WriteLine("**************************************\n\n");
            try
            {
                OleDbConnection oConn = new OleDbConnection();
                OleDbCommand oCmd = new OleDbCommand();
                OleDbDataAdapter oDa = new OleDbDataAdapter();
                DataSet oDs = new DataSet();

                String strDestino = @"cargos.xls";
                oConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDestino + ";Extended Properties=Excel 8.0;";

                oCmd.CommandText = @"select * from Datos";
                oCmd.Connection = oConn;
                oDa.SelectCommand = oCmd;
                oDa.Fill(oDs);
                Cargos tmp = new Cargos();
                foreach (DataRow dtFila in oDs.Tables[0].Rows)
                {
                    if (dtFila[0].ToString().Length > 0)
                    {
                        Console.WriteLine(dtFila[0].ToString() + " " +
                                        "\n");
                        var em = new TCargo();
                        em.NombreCargo = dtFila[0].ToString();
                        em.fechaUltimaGestion = DateTime.Now;
                        em.usuarioUltimaGestion = 1;
                        tmp.AgregarCargo(em);
                    }
                    
                }
            }
            catch (Exception ex)
            {

                Console.WriteLine(ex.Message);

            }
            Console.ReadLine();
        }
        static void subeEmpresas()
        {
            Console.WriteLine("**************************************\n");
            Console.WriteLine("*Importar empresas del Sistema CavTop*\n");
            Console.WriteLine("**************************************\n\n");

            try
            {
                OleDbConnection oConn = new OleDbConnection();
                OleDbCommand oCmd = new OleDbCommand();
                OleDbDataAdapter oDa = new OleDbDataAdapter();
                DataSet oDs = new DataSet();

                String strDestino = @"empresas.xls";
                oConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDestino + ";Extended Properties=Excel 8.0;";

                oCmd.CommandText = @"select * from Datos";
                oCmd.Connection = oConn;
                oDa.SelectCommand = oCmd;
                oDa.Fill(oDs);
                Empresas tmp = new Empresas();

                foreach (DataRow dtFila in oDs.Tables[0].Rows)
                {
                    Console.WriteLine(dtFila[1].ToString() + " " + dtFila[2].ToString() +
                                       dtFila[3].ToString() + " " +
                                       dtFila[4].ToString() + " " +
                                       dtFila[5].ToString() + " " +
                                       dtFila[6].ToString() + " " +
                                       dtFila[7].ToString() + " " +
                                       dtFila[8].ToString() + " " +
                                       dtFila[9].ToString() + " " +
                                        "\n");
                    var em = new TEmpresa();
                    em.nit = dtFila[1].ToString();
                    em.RazonSocial = dtFila[2].ToString();
                    em.telefonos = dtFila[3].ToString();
                    em.telContactoInterno = dtFila[4].ToString();
                    em.EmpleadoContacto = dtFila[5].ToString();
                    em.ObservacioneEmpresa = dtFila[6].ToString();
                    em.fechaUltimaGestion = DateTime.Now;
                    em.usuarioUltimaGestion = 1;
                    em.CargoContacto = dtFila[9].ToString();
                    tmp.CrearEmpresa(em, 1);


                }
            }
            catch (Exception ex)
            {

                Console.WriteLine(ex.Message);
            }
            Console.ReadLine();
        }
    }
}
